﻿using System;
using System.Collections.Generic;
using SurveySystem.Model;
using MySql.Data.MySqlClient;
using SurveySystem.Utility;

namespace SurveySystem.Repository.MySQL
{
    public class MySQLAnswerRepository : ISurveyAnswerRepository
    {
        #region " Private Utility Methods "
        private static Answer AnswerFromDataReader(MySqlDataReader dr)
        {
            Answer a = new Answer();

            a.AnswerID = dr.GetInt32(0);
            if (!(dr.GetValue(1) is System.DBNull))
                a.QuestionID = dr.GetInt32(1);
            if (!(dr.GetValue(2) is System.DBNull))
                a.AnswerText = dr.GetString(2);


            return a;
        }
        #endregion

        public Answer GetAnswerByID(int answerID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            Answer a = null;

            sql = "SELECT `answer_id`, `question_id`, `answer_text` \n" + //
                  "FROM `survey_answer` WHERE answer_id=?id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", answerID);

                conn.Open();
                dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    a = AnswerFromDataReader(dr);

                }
                else
                {
                    a = null;
                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return a;
        }

        public List<Answer> GetAnswersForQuestion(int questionID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<Answer> list = new List<Answer>();

            sql = "SELECT `answer_id`, `question_id`, `answer_text` \n" + //
                  "FROM `survey_answer` WHERE question_id=?id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", questionID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Answer a = AnswerFromDataReader(dr);

                    list.Add(a);
                }


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<Answer> GetAnswersForSurvey(int surveyID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            List<Answer> list = new List<Answer>();

            sql = "SELECT `answer_id`, `question_id`, `answer_text` \n" + //
                  "FROM `survey_answer` WHERE survey_id=?id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?id", surveyID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Answer a = AnswerFromDataReader(dr);

                    list.Add(a);
                }


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public void AddAnswer(Answer a)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;
            MySqlDataReader dr;

            sql = "INSERT INTO  `survey_answer` \n" + //
                  "(`question_id`,`answer_text`) \n" + //
                  "VALUES \n" + //
                  "( ?question_id, ?answer_text)";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("?question_id", a.QuestionID);
                cmd.Parameters.AddWithValue("?answer_text", a.AnswerText);
                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();


                //retrieve the new ID field from the database
                sql = "SELECT @@IDENTITY";
                cmd = new MySqlCommand(sql, conn);
                dr = cmd.ExecuteReader();

                // update the Response object
                if (dr.Read())
                {
                    int newid = dr.GetInt32(0);

                    a.AnswerID = newid;

                }

                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

        public void UpdateAnswer(Answer a)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "UPDATE `survey_answer` SET \n" + //
                  "    `question_id` = ?question_id, \n" + //
                  "    `answer_text` = ?answer_text \n" + //
                  "WHERE `answer_id` = ?answer_id";
            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);

                cmd.Parameters.AddWithValue("?question_id", a.QuestionID);
                cmd.Parameters.AddWithValue("?answer_text", a.AnswerText);
                cmd.Parameters.AddWithValue("?answer_id", a.AnswerID);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

        public void DeleteAnswer(int answerID)
        {
            string sql;
            MySqlConnection conn;
            MySqlCommand cmd;

            sql = "DELETE FROM `survey_answer` \n" + //
                  " WHERE `answer_id` = ?answer_id";

            try
            {
                conn = new MySqlConnection(Globals.ConnectionString);
                cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("?answer_id", answerID);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();


                conn.Close();
            }
            catch (MySqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }
    }
}
